package com.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.poi.ss.usermodel.Sheet;


import jxl.Cell;
import jxl.Workbook;

public class ExcelUtils {

	public static List<HashMap<String, String>> export(String filePath, boolean isNameToFirst) throws Exception{
		List<HashMap<String, String>> list = new ArrayList<HashMap<String,String>>() ;
		InputStream is = new FileInputStream(filePath);
		jxl.Workbook rwb = Workbook.getWorkbook(is);
		int sheetNum = rwb.getNumberOfSheets();
		for (int s = 0; s < sheetNum; s++) {
			jxl.Sheet rs = rwb.getSheet(s);
			int num = rs.getRows();
			HashMap<String, String> columnName = new HashMap<String, String>() ;
			for (int i = 0; i < num; i++) {
				Cell[] cell = rs.getRow(i);
				if(isNameToFirst && i == 0) {
					for (int j = 0; j < cell.length; j++) {
						columnName.put(j + "", cell[j].getContents().trim()) ;
					}
				}else {
					HashMap<String, String> map = new HashMap<String, String>();
					for (int j = 0; j < cell.length; j++) {
						String mapValue = cell[j].getContents().trim();
						if(mapValue.indexOf("/")!=-1 && mapValue.split("/").length>2){
							if(mapValue.split("/").length==3 && mapValue.split("/")[2].length()==4){
								mapValue = mapValue.split("/")[2]+"-"+mapValue.split("/")[1]+"-"+mapValue.split("/")[0];
							}
						}
						if(isNameToFirst){
							map.put(columnName.get(j + ""), mapValue);
						}else {
							map.put(j+"", mapValue);
						}
					}
					list.add(map) ;
				}
			}
		}
		return list ;
	}
	
	public static List<HashMap<String, String>> readExcel2007(String filePath, boolean isNameToFirst, String[] columnNames) {
		List<HashMap<String, String>> list = new ArrayList<HashMap<String,String>>() ;
		InputStream is = null;
		try{
			
			is = new FileInputStream(filePath);
			org.apache.poi.ss.usermodel.Workbook wb = org.apache.poi.ss.usermodel.WorkbookFactory.create(is);
			int  sheetNum = wb.getNumberOfSheets();
			
			for (int i = 0; i < sheetNum; i++) {
				Sheet sheet = wb.getSheetAt(i);
				int rowNum = sheet.getLastRowNum();
				
				//处理数据对应字段名称
				HashMap<String, String> columnMap = new HashMap<String, String>();
				if(isNameToFirst){	//第一行为返回字段名称，将第一行数据导入到列名集合中
					org.apache.poi.ss.usermodel.Row row = sheet.getRow(0);
					int  cellNum = sheet.getRow(0).getLastCellNum();
					for (int k = 0; k < cellNum; k++) {
						org.apache.poi.ss.usermodel.Cell cell = row.getCell(k);
						String cellValue="";
						int cellType = cell.getCellType();
						if(cellType==org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING){
							cellValue = cell.getStringCellValue().trim();
						}if(cellType==org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC){
							cellValue = String.valueOf((long)cell.getNumericCellValue());
						}if(cellType==org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA){
							cellValue = cell.getCellFormula();
						}
						columnMap.put("col_" + k, cellValue) ;
					}
				}else {	//将参数中的字段名称添加到字段集合中
					for (int k = 0; k < columnNames.length; k++) {
						columnMap.put("col_" + k, columnNames[k]) ;
					}
				}
				
				for (int j = 1; j <= rowNum; j++) {
					org.apache.poi.ss.usermodel.Row row = sheet.getRow(j);
					int  cellNum = sheet.getRow(j).getLastCellNum();
					HashMap<String, String> hm = new HashMap<String, String>();
					for (int k = 0; k < cellNum; k++) {
						org.apache.poi.ss.usermodel.Cell cell = row.getCell(k);
						String cellValue="";
						if(cell != null) {
							int cellType = cell.getCellType();
							if(cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING){
								cellValue = cell.getStringCellValue().trim();
							} else if(cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC){
								cellValue = String.valueOf(cell.getNumericCellValue());
							} else if(cellType == org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA){
								cellValue = cell.getCellFormula();
							}
						}
						System.out.print(columnMap.get("col_" + k) + ":" + cellValue + ", ");
						hm.put(columnMap.get("col_" + k), cellValue);
					}
					System.out.println();
					list.add(hm);
				}
			}
		}catch(Exception e){
			e.printStackTrace() ;
		}finally {
			try{
				is.close() ;
			}catch(Exception e){}
		}
		return list;
	}
	
	public static void main(String[] args) {
		String[] columnNames = {
				"CityName", "Name", "IdNo", "BatchNo", "CurrComp", "CurrOutTime", "ExPeriod", "account1", "pcard1", "Org", "CurrCD", "CurrBal", "BillBal", "Tad", "NewCD", "NewCurrBal", "NewTad", "PmtDate", "BillDay", "DueDay", "OutDate", "ExpDate", "USER_SerialNO", "TotalTad", "recoverState"
		};
		readExcel2007("D:/temp/excel_test/20121216_NC.xlsx", false, columnNames) ;
	}
}
